/**
 * Copyright (c) 2007,中企动力华南研发中心<br>
 * All rights reserved.<br>
 */
package com.ce.pms.charge.dao.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.charge.dao.IBankCollectionDao;
import com.ce.pms.charge.model.CollectionFileDomain;
import com.ce.pms.charge.vo.CollectionFileVO;
import com.ce.pms.common.annotation.AssociatedPrecinctQuery;
import com.sitechasia.webx.core.support.Page;

/**
 * 功能描述：<br>
 * @author 易贤建 创建日期：2008-9-22<br>
 */
public class BankCollectionDaoImpl extends PmsBaseDaoImpl implements
		IBankCollectionDao {

	/**
	 * 功能描述:  查询银行出盘列表<br>
	 * @param collectonfilevo
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-22  <br>
	 */
	@AssociatedPrecinctQuery(alias = "cf", beginStr = " AND ", endStr = " ", insertAfterPoint = "where 1=1",property4Precinct="period.chargePrecinct.keyword")
	public Page queryOutBankCollection(CollectionFileVO collectonfilevo) {
		StringBuffer queryStr = new StringBuffer("FROM CollectionFileDomain cf where 1=1");

		Map<String,Object> paramMap = new HashMap<String,Object>();
		String conditionStr = buildQuerCondition(collectonfilevo,paramMap);
		queryStr.append(conditionStr);

		return super.pagedNameQueryByPageNo(queryStr.toString(),
											collectonfilevo.getPageNo(),
											collectonfilevo.getPageSize(),paramMap);
	}
	/**
	 * 功能描述:  查询银行回盘列表<br>
	 * @param collectonfilevo
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-22  <br>
	 */
	@AssociatedPrecinctQuery(alias = "cf", beginStr = " AND ", endStr = " ", insertAfterPoint = "where 1=1",property4Precinct="period.chargePrecinct.keyword")
	public Page queryInBankCollection(CollectionFileVO collectonfilevo){
		StringBuffer queryStr = new StringBuffer("FROM CollectionFileDomain cf where 1=1");
		Map<String,Object> paramMap = new HashMap<String,Object>();
		String conditionStr = buildQuerCondition(collectonfilevo,paramMap);
		queryStr.append(conditionStr);
		queryStr.append(" AND cf.inFileName IS NOT NULL AND  cf.inTime IS NOT NULL AND cf.inMan IS NOT NULL ");

		return super.pagedNameQueryByPageNo(queryStr.toString(),
				collectonfilevo.getPageNo(),
				collectonfilevo.getPageSize(),paramMap);
	}
	/**
	 * 功能描述:查询指定回盘文件名且原先回盘文件为空的信息<br>
	 * @param inFileName
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-7<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public CollectionFileDomain getCollectionFileByInFileName(String inFileName){
		String hql="FROM CollectionFileDomain coll WHERE coll.outFileName=? AND (coll.inFileName='' or coll.inFileName is null)";
		Query query=createQuery(hql, inFileName.trim());
		return (CollectionFileDomain)query.uniqueResult();
	}

	/**
	 * 功能描述:给一个银行ID，差托收文件表中ID最大的domain<br>
	 * @param id
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-10-30  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	@AssociatedPrecinctQuery(alias = "coll", beginStr = " AND ", endStr = " ", insertAfterPoint = "WHERE 1=1",property4Precinct="period.chargePrecinct.keyword")
	public CollectionFileDomain getCollectionFileDomainForMaxKeyword(Long id){
		String hql="FROM CollectionFileDomain coll WHERE 1=1 AND coll.keyword in(SELECT max(colls.keyword) FROM CollectionFileDomain colls where colls.bank.keyword=? )";
		Query query=createQuery(hql, id);
		List<CollectionFileDomain> list = query.list();
		CollectionFileDomain domain = null;
		if(list != null && list.size() > 0 )
			domain = list.get(0);
		return domain;
	}
	/**
	 * 功能描述:得到当天出盘文件的最大的流水序号<br>
	 * @param prifix
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-28<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public int getMaxOutFileNameSeq(){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT  max(cast(SUBSTRING_INDEX(OUT_FILE_NAME,'-',-1)  as  SIGNED ))")
		.append(" FROM FEE_COLLECTION_FILE ")
		.append(" WHERE CAST(OUT_TIME as Date)=CAST(now() as Date) ");

		Query query=createSQLQuery(sql.toString(),null);
		Object result=query.uniqueResult();
		return result==null ? 0 : ((BigInteger)result).intValue();
	}

	/**
	 * 功能描述:根据ID查询银行出盘domain<br>
	 * @param collectonfilevo
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-9-22  <br>
	 */
	public CollectionFileDomain getCollectionFileById(Long keyword){
		String queryStr = "FROM CollectionFileDomain cf where cf.keyword=?";
		Query query = super.createQuery(queryStr, keyword);
		return (CollectionFileDomain)query.uniqueResult();
	}
	/**
	 * 功能描述:在托收明细中,查询银行帐号对应托收的记录数<br>
	 * 如果指定状态,则查询银行帐号对应的托收指定状态的记录数
	 * @param bankAccount
	 * @param status
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-10-24  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public Long getBankAccountCountFromCollectionDetail(String bankAccount,String ... status){
		StringBuffer queryStr = new StringBuffer(" SELECT COUNT(collectionDetail.ACC_NUMBER) ");
		queryStr.append(" FROM FEE_COLLECTION_DETAIL collectionDetail ");
		queryStr.append(" WHERE 1=1 AND collectionDetail.ACC_NUMBER=? ");
		queryStr.append(buildQueryConditionForBankAccountCount("collectionDetail",status));

		Object[] params = getParamQueryConditionForBankAccountCount(bankAccount,status);
		Query query = super.createSQLQuery(queryStr.toString(), params);
		Object result = query.uniqueResult();
		return ((BigInteger)result).longValue();
	}

	/**
	 * 功能描述:构建查询条件,查询状态的OR<br>
	 * @param alias
	 * @param status
	 * @return
	 * Create author:yixianjian <br>
	 * Create on:2008-10-24  <br>
	 */
	private String buildQueryConditionForBankAccountCount(String alias,String[] status){
		if(status == null || status.length == 0 ) return "";
		StringBuffer statusStr = new StringBuffer();
		statusStr.append(" AND ").append(alias).append(".STATUZ=?").append(" ");
		for(int i = 1; i < status.length; i ++){
			statusStr.append(" OR ").append(alias).append(".STATUZ=?").append(" ");
		}
		return statusStr.toString();
	}
	private Object[] getParamQueryConditionForBankAccountCount(String bankAccount,String ... status){
		Object[] params = new Object[status.length + 1];
		params[0] = bankAccount;
		for(int i = 0;status != null && i < status.length; i++)
			params[i+1] = status[i];
		return params;
	}
	/*
	 * 构建查询条件
	 */
	private String buildQuerCondition(CollectionFileVO collection,Map<String,Object> paramMap){
		if(collection == null) return null;
		StringBuffer strBuf = new StringBuffer();
		//银行ID
		if(collection.getBankId() != null){
			strBuf.append(" AND cf.bank.keyword=:bankId ");
			paramMap.put("bankId", collection.getBankId());
		}//收费周期
		if(collection.getPeriodId() != null){
			strBuf.append(" AND cf.period.keyword=:periodId ");
			paramMap.put("periodId", collection.getPeriodId());
		}
		return strBuf.toString();
	}
}
